Imports System.Data.SqlClient
Public Class frmChiTiet

    Private Sub btnIn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnIn.Click
        Dim TuNgay As Date
        Dim DenNgay As Date
        If radTheoThang.Checked Then
            TuNgay = New Date(CInt(txtNam.Text), CInt(cbxThang.Text), 1)
            DenNgay = New Date(CInt(txtNam.Text), CInt(cbxThang.Text), Date.DaysInMonth(CInt(txtNam.Text), CInt(cbxThang.Text)))
        Else
            TuNgay = dtpTuNgay.Value
            DenNgay = dtpDenNgay.Value
        End If

        Dim strPath = Application.StartupPath & "\templates\bao_cao_chi_tiet.xls"
        Dim strSPath = Application.StartupPath & "\temp\BaoCaoChiTiet_" & Now.ToString("ddMMyyyy_") & Now.Millisecond & ".xls"
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Try

            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False

            xlWorkBook = xlApp.Workbooks.Open(strPath)
            xlWorkBook.SaveCopyAs(strSPath)
            xlWorkBook.Close()
            xlWorkBook = xlApp.Workbooks.Open(strSPath)
            xlWorkSheet = xlWorkBook.Worksheets("SHN")
            'edit the cell with new value
            xlWorkSheet.Cells(1, 1).value = aryConfig.Item("CFG01")
            xlWorkSheet.Cells(2, 1).value = xlWorkSheet.Cells(2, 1).value.ToString.Replace("{DiaChi}", aryConfig.Item("CFG02"))
            xlWorkSheet.Cells(3, 1).value = xlWorkSheet.Cells(3, 1).value.ToString.Replace("{MST}", aryConfig.Item("CFG04"))
            xlWorkSheet.Cells(6, 1).value = xlWorkSheet.Cells(6, 1).value.ToString.Replace("{MaHH}", cbxHangHoa.SelectedValue)
            xlWorkSheet.Cells(6, 1).value = xlWorkSheet.Cells(6, 1).value.ToString.Replace("{TenHH}", cbxHangHoa.Text)

            Dim strNgay As String = xlWorkSheet.Cells(7, 1).value.ToString.Replace("{TuNgay}", TuNgay.ToString("dd/MM/yyyy"))
            strNgay = strNgay.Replace("{DenNgay}", DenNgay.ToString("dd/MM/yyyy"))
            xlWorkSheet.Cells(7, 1).value = strNgay

            Dim strNgayXuat As String = xlWorkSheet.Cells(15, 11).value.ToString.Replace("{NgayCT}", Now.Day)
            strNgayXuat = strNgayXuat.Replace("{ThangCT}", Now.Month)
            strNgayXuat = strNgayXuat.Replace("{NamCT}", Now.Year)
            xlWorkSheet.Cells(15, 11).value = strNgayXuat

            xlWorkSheet.Cells(21, 1).value = aryConfig.Item("CFG09")
            xlWorkSheet.Cells(21, 5).value = aryConfig.Item("CFG08")
            xlWorkSheet.Cells(21, 11).value = aryConfig.Item("CFG06")

            Dim tblButToan As DataTable = LayDanhSachButToan(cbxHangHoa.SelectedValue, TuNgay, DenNgay)
            If tblButToan.Rows.Count > 0 Then
                xlWorkSheet.Cells(11, 11).value = tblButToan.Rows(0)(5)
                xlWorkSheet.Cells(11, 12).value = tblButToan.Rows(0)(6)
                Dim i As Integer = 0
                For i = 0 To tblButToan.Rows.Count - 1
                    Dim row As DataRow = tblButToan.Rows(i)
                    xlWorkSheet.Cells(12 + i, 1).value = row(1)
                    xlWorkSheet.Cells(12 + i, 2).value = row(9)
                    xlWorkSheet.Cells(12 + i, 3).value = row(10)
                    xlWorkSheet.Cells(12 + i, 4).value = row(8)
                    If row(1) = "PNK" Then
                        xlWorkSheet.Cells(12 + i, 5).value = "'1561"
                        xlWorkSheet.Cells(12 + i, 6).value = "'3311"
                        xlWorkSheet.Cells(12 + i, 7).value = row(2)
                        xlWorkSheet.Cells(12 + i, 8).value = row(3) + row(4)
                        xlWorkSheet.Cells(12 + i, 9).value = 0
                        xlWorkSheet.Cells(12 + i, 10).value = 0
                    Else
                        xlWorkSheet.Cells(12 + i, 5).value = "'632"
                        xlWorkSheet.Cells(12 + i, 6).value = "'1561"
                        xlWorkSheet.Cells(12 + i, 9).value = row(2)
                        xlWorkSheet.Cells(12 + i, 10).value = row(2) * row(7)
                        xlWorkSheet.Cells(12 + i, 7).value = 0
                        xlWorkSheet.Cells(12 + i, 8).value = 0
                    End If
                    xlWorkSheet.Cells(12 + i, 11).value = "=K" & (11 + i) & "+G" & (12 + i) & "-I" & (12 + i)
                    xlWorkSheet.Cells(12 + i, 12).value = "=L" & (11 + i) & "+H" & (12 + i) & "-J" & (12 + i)

                    If i <> tblButToan.Rows.Count - 1 Then
                        xlWorkSheet.Rows(13 + i).Select()
                        xlApp.Selection.Insert(Excel.XlInsertShiftDirection.xlShiftDown)
                        xlWorkSheet.Rows(12).Select()
                        xlApp.Selection.Copy()
                        xlWorkSheet.Rows(13 + i).Select()
                        xlApp.ActiveSheet.Paste()
                    End If
                Next

                xlWorkSheet.Cells(13 + i - 1, 7).value = "=Sum(G12:G" & (13 + i - 2) & ")"
                xlWorkSheet.Cells(13 + i - 1, 8).value = "=Sum(H12:H" & (13 + i - 2) & ")"
                xlWorkSheet.Cells(13 + i - 1, 9).value = "=Sum(I12:I" & (13 + i - 2) & ")"
                xlWorkSheet.Cells(13 + i - 1, 10).value = "=Sum(J12:J" & (13 + i - 2) & ")"

            Else
                tblButToan = LayButToanTruocDo(cbxHangHoa.SelectedValue, TuNgay)
                If tblButToan.Rows.Count > 0 Then
                    xlWorkSheet.Cells(11, 11).value = tblButToan.Rows(0)(5) + tblButToan.Rows(0)(2)
                    xlWorkSheet.Cells(11, 12).value = tblButToan.Rows(0)(6) + tblButToan.Rows(0)(3) + tblButToan.Rows(0)(4)
                Else
                    tblButToan = TruyVanDanhSach("Select SLTonDK, GTTonDK, GiaVonDK from CTHangHoa where MaHH = '" & cbxHangHoa.SelectedValue & "' and Thang = " & TuNgay.Month & " and Nam = " & TuNgay.Year)
                    If tblButToan.Rows.Count > 0 Then
                        xlWorkSheet.Cells(11, 11).value = tblButToan.Rows(0)(0)
                        xlWorkSheet.Cells(11, 12).value = tblButToan.Rows(0)(1)
                    End If
                End If
            End If


            xlWorkBook.Save()
            xlWorkSheet.Cells(4, 1).Select()
            xlApp.Selection.Copy()
            xlApp.Visible = True
            xlWorkBook.Activate()
            xlApp.DisplayAlerts = True

        Catch ex As Exception

        End Try
    End Sub

    Private Sub btnDong_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDong.Click
        Me.Close()
    End Sub

    Function LayDanhSachButToan(ByVal MaHH As String, ByVal TuNgay As Date, ByVal DenNgay As Date) As DataTable
        Dim strSql As String = "Select "
        strSql &= " ct.MaPhieuCT, "
        strSql &= " sc.LoaiCT, "
        strSql &= " ct.SoLuong, "
        strSql &= " case "
        strSql &= " when ct.USD = 1 then sc.TyGia * ct.SoLuong * ct.DonGia "
        strSql &= "	else ct.SoLuong * ct.DonGia "
        strSql &= " end as TienHang, "
        strSql &= " ct.ThueNK, "
        strSql &= " ct.TonKho, "
        strSql &= " ct.GiaTri, "
        strSql &= " ct.GiaVon, "
        strSql &= " sc.GhiChu, "
        strSql &= " sc.SoCT, "
        strSql &= " convert(Varchar, sc.NgayCT,103) as NgayCT "
        strSql &= " from"
        strSql &= " CTSoCai ct"
        strSql &= " inner join SoCai sc on ct.MaPhieu = sc.MaPhieu"
        strSql &= " where "
        strSql &= " sc.NgayCT >= convert(DATETIME, @From, 103) "
        strSql &= " and sc.NgayCT <= convert(DATETIME, @To, 103) "
        strSql &= " and ct.MaHH = @MaHH "
        strSql &= " order by sc.NgayCT ASC, sc.LoaiCT"
        Dim myConn As New SqlConnection(modKetNoi.strConnectionString)
        Dim myTable As New DataTable
        Try
            myConn.Open()
            Dim myAdapter As New SqlDataAdapter(strSql, myConn)
            myAdapter.SelectCommand.Parameters.AddWithValue("@From", TuNgay.ToString("dd/MM/yyyy"))
            myAdapter.SelectCommand.Parameters.AddWithValue("@To", DenNgay.ToString("dd/MM/yyyy"))
            myAdapter.SelectCommand.Parameters.AddWithValue("@MaHH", MaHH)
            myAdapter.Fill(myTable)
        Catch ex As Exception

        End Try
        myConn.Close()
        myConn.Dispose()
        Return myTable
    End Function

    Function LayButToanTruocDo(ByVal MaHH As String, ByVal TuNgay As Date) As DataTable
        Dim strSql As String = "Select Top 1 "
        strSql &= " ct.MaPhieuCT, "
        strSql &= " sc.LoaiCT, "
        strSql &= " ct.SoLuong, "
        strSql &= " case "
        strSql &= " when ct.USD = 1 then sc.TyGia * ct.SoLuong * ct.DonGia "
        strSql &= "	else ct.SoLuong * ct.DonGia "
        strSql &= " end as TienHang, "
        strSql &= " ct.ThueNK, "
        strSql &= " ct.TonKho, "
        strSql &= " ct.GiaTri, "
        strSql &= " ct.GiaVon "
        strSql &= " from"
        strSql &= " CTSoCai ct"
        strSql &= " inner join SoCai sc on ct.MaPhieu = sc.MaPhieu"
        strSql &= " where "
        strSql &= " sc.NgayCT < convert(DATETIME, @From, 103) "
        strSql &= " and datepart(Month, sc.NgayCT) = @Thang "
        strSql &= " and datePart(Year, sc.NgayCT) = @Nam "
        strSql &= " and ct.MaHH = @MaHH "
        strSql &= " order by sc.NgayCT DESC, sc.LoaiCT"
        Dim myConn As New SqlConnection(modKetNoi.strConnectionString)
        Dim myTable As New DataTable
        Try
            myConn.Open()
            Dim myAdapter As New SqlDataAdapter(strSql, myConn)
            myAdapter.SelectCommand.Parameters.AddWithValue("@From", TuNgay.ToString("dd/MM/yyyy"))
            myAdapter.SelectCommand.Parameters.AddWithValue("@MaHH", MaHH)
            myAdapter.SelectCommand.Parameters.AddWithValue("@Thang", TuNgay.Month)
            myAdapter.SelectCommand.Parameters.AddWithValue("@Nam", TuNgay.Year)
            myAdapter.Fill(myTable)
        Catch ex As Exception

        End Try
        myConn.Close()
        myConn.Dispose()
        Return myTable
    End Function

    Private Sub frmChiTiet_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim myTable As DataTable = TruyVanDanhSach("select hh.MaHH, hh.TenHH, dv.TenDV from HangHoa hh Left Join DonVi dv on hh.MaDV = dv.MaDV")
        Me.cbxHangHoa.DataSource = myTable
        Me.cbxHangHoa.DisplayMember = "TenHH"
        Me.cbxHangHoa.ValueMember = "MaHH"
        Me.cbxHangHoa.SelectedIndex = 0
    End Sub

End Class